package patrol.mobile.com.mobilepatrol;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

import com.google.gson.internal.LinkedTreeMap;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import patrol.mobile.com.mobilepatrol.activity.road_block.AssetTongzuxinxi;
import patrol.mobile.com.mobilepatrol.activity.route_query.RouteManage;
import patrol.mobile.com.mobilepatrol.activity.route_query.RouteType;
import patrol.mobile.com.mobilepatrol.bean.InitDownLoadInfoBean;
import patrol.mobile.com.mobilepatrol.bean.RouteSurveyTaskBean;
import patrol.mobile.com.mobilepatrol.bean.RouteSurveyTaskDataBO;
import patrol.mobile.com.mobilepatrol.bean.RouteSurveyTaskDetailBean;
import patrol.mobile.com.mobilepatrol.bean.RouteSurveyTaskItemBO;
import patrol.mobile.com.mobilepatrol.bean.RouteSurveyTaskStructBO;
import patrol.mobile.com.mobilepatrol.bean.TrafficInvestBaseInfo;

/**
 * Created by apple on 17/12/27.
 */

public class DBHelper extends SQLiteOpenHelper{
    public final static String DBNAME = "dataInfo.db";
    private final static int VERSION = 1;
    public DBHelper(Context context) {
        super(context, DBNAME, null, VERSION);
    }
    private final static String CREATE_TB_HEAD = "create table if not exists ";
    private static String createRoadSurveyTable; //创建路况调查表格
    private static String createSurveyTaskStructTable;//创建 任务明细生成公里结构树 表格
    private final static String TASKDATATB ="taskData";
    private final static String TASKSTRUCTTB="taskStruct";
    private static String createSurveyTaskDataTable;//创建 任务明细数据 表格
    private static String createSurveyTaskTable; //创建路况调查任务表格
    private static String ALTERTASKDATETB="alter table "+TASKDATATB+" add column updatetime long";
    private final static String SURVEY_ROUTE_ITEMTB = "survey_route_item_info";
    private final static String ROADSURVEYTB = "roadsurvey";
    private final static String SURVEYTASKTB = "survey_task"; //路况调查任务
    private final static String SURVEY_ROUTETB = "survey_routeinfo";
    private static String createSurveyTaskRouteTable; //创建路况调查任务路线表格
    private static String createSurveyTaskRouteItemTable; //创建路况调查任务路线对应路段表格

    private final static String ROUTEMANAGE = "routemanage";
    private final static String ATongzuxinxi = "AssetTongzuxinxi";
    private static String createRouteManage;//创建 任务明细数据 表格
    private static String createAssetTongzuxinxi;//创建 任务明细数据 表格
    static {
        createSurveyTaskRouteItemTable = CREATE_TB_HEAD + SURVEY_ROUTE_ITEMTB +
                "(ID TEXT PRIMARY KEY,taskDetailID text,startPot text,endPot text,startPotKm int,startPotMeter int,endPotKm int,endPotMeter int," +
                "roudBedType text,routeGrade text,potPeriod text,surveyType text)";
        createSurveyTaskRouteTable = CREATE_TB_HEAD + SURVEY_ROUTETB +
                "(ID TEXT PRIMARY KEY,taskID text,routeID text,routeName TEXT,startPot text," +
                "endPot text,startPotKm int,startPotMeter int,endPotKm int,endPotMeter int)";
        createSurveyTaskTable = CREATE_TB_HEAD + SURVEYTASKTB +
                "(ID TEXT PRIMARY KEY,org_No TEXT,taskName TEXT)";
        createSurveyTaskStructTable = CREATE_TB_HEAD + TASKSTRUCTTB +
                "(ID TEXT PRIMARY KEY,taskDetailID text,name text,startPotName text,endPotName TEXT,roadWidth real,roadLength real," +
                "Pot text,PotKm int,PotMeter int,state text,childid text)";
        createSurveyTaskDataTable=CREATE_TB_HEAD+ TASKDATATB +
                "(ID text PRIMARY KEY,columnID text, columnName text, unit text, nickName text, structID text, type text, surveyOne real,surveyTwo real,surveyThree real,surveyFour real,surveyFive real,surveySix real,surveySeven real,surveyEight real,surveyNine real,surveyTen real,checkOne real,checkTwo real,checkThree real,checkFour real,checkFive real,checkSix real,checkSeven real,checkEight real,checkNine real,checkTen real,sortID int,isupdated int,updatetime long,childpos int)";
        //添加路面
        createRoadSurveyTable = CREATE_TB_HEAD + ROADSURVEYTB +
                "(ID TEXT PRIMARY KEY,DetailID text,surveyLine TEXT,roudBedType TEXT,startPot text,endPot TEXT,surveyDate TEXT," +
                "surveyer TEXT,surveyerID int,orgName TEXT,orgID TEXT,status int,weather text," +
                "roadCar text,startPotKm int,startPotMeter int,endPotKm2 int,endPotMeter2 int," +
                "roadLevel text,surveyType TEXT,surveyWay TEXT,surveyTask TEXT,remark TEXT,timeStamp long,surveySelect TEXT,luji int,lumian int,qiaosui int,yanxian int)";


        createRouteManage=CREATE_TB_HEAD+ ROUTEMANAGE +
                "(ID text PRIMARY KEY,routeId text, routeCode text, routeName text, startPot text, endPot text, custodyRouteMileage text, custodyRouteType text,orgId text,orgName text)";
        createAssetTongzuxinxi=CREATE_TB_HEAD+ ATongzuxinxi +
                "(id text PRIMARY KEY,status text, blockTime text, routeCoding text, routeId text, startLocation text, endLocation text, description text,recoveryTime text,plan text,startPot text,startPotK text,startPotKm text,startPotM text,endPot text,endPotK text,endPotKm text,endPotM text,upDown text,title text,orgId text,orgName text,smoothreCoveryTime text,reportingStatus text,isRetimeUp text,isDataUp text,imgs text)";

    }
    public void insertTaskDataInfo(List<LinkedTreeMap> infos){
        if(infos==null||infos.size()<=0)
            return;
        Log.e(this.getClass().getSimpleName(),"init insertTaskDataInfo:"+infos.size());
        SQLiteDatabase db = getWritableDatabase();
        //  db.delete(TASKDATATB,null,null);
        ContentValues cv;
        db.beginTransaction();
        for(LinkedTreeMap info:infos){
            cv = new ContentValues();
            cv.put("ID", info.get("iD").toString());
            cv.put("columnID", info.get("columnID").toString() );
            cv.put("columnName", info.get("columnName").toString() );
            cv.put("unit", info.get("unit").toString() );
            cv.put("nickName", info.get("nickName").toString() );
            cv.put("structID",info.get("structID").toString());
            cv.put("type", info.get("type").toString() );
            cv.put("surveyOne", info.get("surveyOne").toString());
            cv.put("surveyTwo", info.get("surveyTwo").toString() );
            cv.put("surveyThree", info.get("surveyThree").toString() );
            cv.put("surveyFour",info.get("surveyFour").toString() );

            cv.put("surveyFive", info.get("surveyFive").toString() );
            cv.put("surveySix", info.get("surveySix").toString() );
            cv.put("surveySeven",info.get("surveySeven").toString());
            cv.put("surveyEight",info.get("surveyEight").toString() );
            cv.put("surveyNine", info.get("surveyNine").toString());
            cv.put("surveyTen",info.get("surveyTen").toString() );
            cv.put("checkOne", info.get("checkOne").toString());
            cv.put("checkTwo", info.get("checkTwo").toString());
            cv.put("checkThree", info.get("checkThree").toString() );
            cv.put("checkFour", info.get("checkFour").toString());
            cv.put("checkFive",info.get("checkFive").toString());
            cv.put("checkSix", info.get("checkSix").toString());
            cv.put("checkSeven", info.get("checkSeven").toString());
            cv.put("checkEight", info.get("checkEight").toString());
            cv.put("checkNine",info.get("checkNine").toString() );
            cv.put("checkTen",info.get("checkTen").toString() );
            cv.put("sortID", info.get("sortID").toString() );
            cv.put("isupdated",0);
            cv.put("updatetime",System.currentTimeMillis());

            db.insert(TASKDATATB, null, cv);
        }
        db.setTransactionSuccessful();
        db.endTransaction();
        db.close();
    }
    public List<RouteSurveyTaskDataBO> getTaskDataInfo(String structID, String type){
        List<RouteSurveyTaskDataBO> infos=new ArrayList<RouteSurveyTaskDataBO>();
        SQLiteDatabase db = getReadableDatabase();

        RouteSurveyTaskDataBO info=null;
        //  Cursor cur=db.query(TASKDATATB, null, null, null, null, null, null,"20");
        Cursor cur=db.query(TASKDATATB, null, "structID=? and type=?", new String[]{structID,type}, null, null, "sortID asc");
        int column;
        while (cur.moveToNext()) {
            column = 0;
            info=new RouteSurveyTaskDataBO();
            info.setiD(cur.getString(column++));
            info.setColumnID(cur.getString(column++)) ;
            info.setColumnName(cur.getString(column++)) ;
            info.setUnit(cur.getString(column++)) ;
            info.setNickName(cur.getString(column++)) ;
            info.setStructID(cur.getString(column++)) ;
            info.setType(cur.getString(column++)) ;
            info.setSurveyOne(cur.getDouble(column++)) ;
            info.setSurveyTwo(cur.getDouble(column++)) ;
            info.setSurveyThree(cur.getDouble(column++)) ;
            info.setSurveyFour(cur.getDouble(column++)) ;
            info.setSurveyFive(cur.getDouble(column++)) ;
            info.setSurveySix(cur.getDouble(column++)) ;
            info.setSurveySeven(cur.getDouble(column++)) ;
            info.setSurveyEight(cur.getDouble(column++)) ;
            info.setSurveyNine(cur.getDouble(column++)) ;
            info.setSurveyTen(cur.getDouble(column++));

            info.setCheckOne(cur.getDouble(column++)) ;
            info.setCheckTwo(cur.getDouble(column++)) ;
            info.setCheckThree(cur.getDouble(column++)) ;
            info.setCheckFour(cur.getDouble(column++)) ;
            info.setCheckFive(cur.getDouble(column++)) ;
            info.setCheckSix(cur.getDouble(column++)) ;
            info.setCheckSeven(cur.getDouble(column++)) ;
            info.setCheckEight(cur.getDouble(column++)) ;
            info.setCheckNine(cur.getDouble(column++)) ;
            info.setCheckTen(cur.getDouble(column++));

            info.setSortID(cur.getInt(column));

            infos.add(info);
        }
        cur.close();
        db.close();

        return infos;
    }
    public List<RouteSurveyTaskDataBO> getTaskDataInfo(){
        List<RouteSurveyTaskDataBO> infos=new ArrayList<RouteSurveyTaskDataBO>();
        SQLiteDatabase db = getReadableDatabase();

        RouteSurveyTaskDataBO info=null;
        //  Cursor cur=db.query(TASKDATATB, null, null, null, null, null, null,"20");
        Cursor cur=db.query(TASKDATATB, null, null, null, null, null, "sortID asc");
        int column;
        while (cur.moveToNext()) {
            column = 0;
            info=new RouteSurveyTaskDataBO();
            info.setiD(cur.getString(column++));
            info.setColumnID(cur.getString(column++)) ;
            info.setColumnName(cur.getString(column++)) ;
            info.setUnit(cur.getString(column++)) ;
            info.setNickName(cur.getString(column++)) ;
            info.setStructID(cur.getString(column++)) ;
            info.setType(cur.getString(column++)) ;
            info.setSurveyOne(cur.getDouble(column++)) ;
            info.setSurveyTwo(cur.getDouble(column++)) ;
            info.setSurveyThree(cur.getDouble(column++)) ;
            info.setSurveyFour(cur.getDouble(column++)) ;
            info.setSurveyFive(cur.getDouble(column++)) ;
            info.setSurveySix(cur.getDouble(column++)) ;
            info.setSurveySeven(cur.getDouble(column++)) ;
            info.setSurveyEight(cur.getDouble(column++)) ;
            info.setSurveyNine(cur.getDouble(column++)) ;
            info.setSurveyTen(cur.getDouble(column++));

            info.setCheckOne(cur.getDouble(column++)) ;
            info.setCheckTwo(cur.getDouble(column++)) ;
            info.setCheckThree(cur.getDouble(column++)) ;
            info.setCheckFour(cur.getDouble(column++)) ;
            info.setCheckFive(cur.getDouble(column++)) ;
            info.setCheckSix(cur.getDouble(column++)) ;
            info.setCheckSeven(cur.getDouble(column++)) ;
            info.setCheckEight(cur.getDouble(column++)) ;
            info.setCheckNine(cur.getDouble(column++)) ;
            info.setCheckTen(cur.getDouble(column++));

            info.setSortID(cur.getInt(column));

            infos.add(info);
        }
        cur.close();
        db.close();

        return infos;
    }



    /**
     * 插入 路况调查基本信息 数据
     *
     * @param investInfo
     */
    public void insertInvestBaseInfo(TrafficInvestBaseInfo investInfo) {
        SQLiteDatabase db = getWritableDatabase();
        ContentValues cv = new ContentValues();

        cv.put("ID", investInfo.getID());
        cv.put("DetailID", investInfo.getDetailID());
        cv.put("surveyLine", investInfo.getSurveyLine());
        cv.put("roudBedType", investInfo.getRoudBedType());
        cv.put("startPot", investInfo.getStartPot());
        cv.put("endPot", investInfo.getEndPot());
        cv.put("surveyDate", investInfo.getSurveyDate());
        cv.put("surveyer", investInfo.getSurveyer());
        cv.put("surveyerID", investInfo.getSurveyerID());
        cv.put("orgName", investInfo.getOrgName());
        cv.put("orgID", investInfo.getOrgID());
        cv.put("status", investInfo.getStatus());
        cv.put("weather", investInfo.getWeather());
        cv.put("roadCar", investInfo.getRoadCar());
        cv.put("startPotKm", investInfo.getStartPotKm());
        cv.put("startPotMeter", investInfo.getStartPotMeter());
        cv.put("endPotKm2", investInfo.getEndPotKm2());
        cv.put("endPotMeter2", investInfo.getEndPotMeter2());
        cv.put("roadLevel",investInfo.getRoadLevel());
        cv.put("surveyType", investInfo.getSurveyType());
        cv.put("surveySelect", investInfo.getSurveySelect());
        cv.put("surveyWay", investInfo.getSurveyWay());
        cv.put("surveyTask", investInfo.getSurveyTask());
        cv.put("remark", investInfo.getRemark());
        cv.put("timeStamp", investInfo.getTimeStamp());
        cv.put("luji",investInfo.getLuji());
        cv.put("lumian",investInfo.getLumian());
        cv.put("qiaosui",investInfo.getQiaosui());
        cv.put("yanxian",investInfo.getYanxian());
        db.insert(ROADSURVEYTB, null, cv);

        db.close();
    }
    public int updateInvestBaseInfo(TrafficInvestBaseInfo investInfo) {
        SQLiteDatabase db = getWritableDatabase();
        ContentValues cv = new ContentValues();

        cv.put("DetailID", investInfo.getDetailID());
        cv.put("surveyLine", investInfo.getSurveyLine());
        cv.put("roudBedType", investInfo.getRoudBedType());
        cv.put("startPot", investInfo.getStartPot());
        cv.put("endPot", investInfo.getEndPot());
        cv.put("status", investInfo.getStatus());
        cv.put("weather", investInfo.getWeather());
        cv.put("roadCar", investInfo.getRoadCar());
        cv.put("startPotKm", investInfo.getStartPotKm());
        cv.put("startPotMeter", investInfo.getStartPotMeter());
        cv.put("endPotKm2", investInfo.getEndPotKm2());
        cv.put("endPotMeter2", investInfo.getEndPotMeter2());
        cv.put("roadLevel",investInfo.getRoadLevel());
        cv.put("surveyType", investInfo.getSurveyType());
        cv.put("surveyWay", investInfo.getSurveyWay());
        cv.put("surveyTask", investInfo.getSurveyTask());
        cv.put("surveySelect", investInfo.getSurveySelect());
        cv.put("remark", investInfo.getRemark());
        cv.put("timeStamp", investInfo.getTimeStamp());
        cv.put("luji",investInfo.getLuji());
        cv.put("lumian",investInfo.getLumian());
        cv.put("qiaosui",investInfo.getQiaosui());
        cv.put("yanxian",investInfo.getYanxian());
        int num = db.update(ROADSURVEYTB, cv, "ID=?", new String[]{investInfo.getID()});

        db.close();

        return num;
    }
    public void deleteInvestBaseInfo(TrafficInvestBaseInfo investInfo){
        SQLiteDatabase db = getWritableDatabase();
        db.delete(ROADSURVEYTB,"ID=?", new String[]{investInfo.getID()});
        db.close();
    }
    /**
     * 获取所有 路况调查基本信息
     */
    public List<TrafficInvestBaseInfo> getInvestBaseInfoAll() {
        List<TrafficInvestBaseInfo> investBaseInfos = new ArrayList<TrafficInvestBaseInfo>();
        SQLiteDatabase db = getReadableDatabase();
        Cursor cur = db.query(ROADSURVEYTB, null, null, null, null, null, null);

        TrafficInvestBaseInfo baseInfo;
        int column;
        int index=0;
        while (cur.moveToNext()) {
            column = 0;
            index = index+1;
            baseInfo = new TrafficInvestBaseInfo();

            baseInfo.setID(cur.getString(column++));
            baseInfo.setDetailID(cur.getString(column++));
            baseInfo.setSurveyLine(cur.getString(column++));
            baseInfo.setRoudBedType(cur.getString(column++));
            baseInfo.setStartPot(cur.getString(column++));
            baseInfo.setEndPot(cur.getString(column++));
            baseInfo.setSurveyDate(cur.getString(column++));
            baseInfo.setSurveyer(cur.getString(column++));
            baseInfo.setSurveyerID(cur.getString(column++));
            baseInfo.setOrgName(cur.getString(column++));
            baseInfo.setOrgID(cur.getString(column++));
            baseInfo.setStatus(cur.getInt(column++));
            baseInfo.setWeather(cur.getString(column++));
            baseInfo.setRoadCar(cur.getString(column++));
            baseInfo.setStartPotKm(cur.getInt(column++));
            baseInfo.setStartPotMeter(cur.getInt(column++));
            baseInfo.setEndPotKm2(cur.getInt(column++));
            baseInfo.setEndPotMeter2(cur.getInt(column++));

            baseInfo.setRoadLevel(cur.getString(column++));
            baseInfo.setSurveyType(cur.getString(column++));
            baseInfo.setSurveyWay(cur.getString(column++));
            baseInfo.setSurveyTask(cur.getString(column++));
            baseInfo.setSurveySelect(cur.getString(column++));
            baseInfo.setRemark(cur.getString(column++));
            baseInfo.setTimeStamp(cur.getLong(column++));
            baseInfo.setLuji(cur.getInt(column++));
            baseInfo.setLumian(cur.getInt(column++));
            baseInfo.setQiaosui(cur.getInt(column++));
            baseInfo.setYanxian(cur.getInt(column));
            baseInfo.setIndex(index);

            investBaseInfos.add(baseInfo);
        }
        cur.close();
        db.close();

        return investBaseInfos;
    }
    /**
     * 分局获取
     * @param orgName
     * @return
     */

    public List<TrafficInvestBaseInfo> getInvestBaseInfoAll(String orgName) {
        List<TrafficInvestBaseInfo> investBaseInfos = new ArrayList<TrafficInvestBaseInfo>();
        SQLiteDatabase db = getReadableDatabase();
        String s="select * from roadsurvey where orgName='"+orgName+"'";
        Cursor cur=db.rawQuery(s, null);
        TrafficInvestBaseInfo baseInfo;
        int column;
        int index = 0 ;
        while (cur.moveToNext()) {
            column = 0;
            index = index+1;
            baseInfo = new TrafficInvestBaseInfo();

            baseInfo.setID(cur.getString(column++));
            baseInfo.setDetailID(cur.getString(column++));
            baseInfo.setSurveyLine(cur.getString(column++));
            baseInfo.setRoudBedType(cur.getString(column++));
            baseInfo.setStartPot(cur.getString(column++));
            baseInfo.setEndPot(cur.getString(column++));
            baseInfo.setSurveyDate(cur.getString(column++));
            baseInfo.setSurveyer(cur.getString(column++));
            baseInfo.setSurveyerID(cur.getString(column++));
            baseInfo.setOrgName(cur.getString(column++));
            baseInfo.setOrgID(cur.getString(column++));
            baseInfo.setStatus(cur.getInt(column++));
            baseInfo.setWeather(cur.getString(column++));
            baseInfo.setRoadCar(cur.getString(column++));
            baseInfo.setStartPotKm(cur.getInt(column++));
            baseInfo.setStartPotMeter(cur.getInt(column++));
            baseInfo.setEndPotKm2(cur.getInt(column++));
            baseInfo.setEndPotMeter2(cur.getInt(column++));

            baseInfo.setRoadLevel(cur.getString(column++));
            baseInfo.setSurveyType(cur.getString(column++));
            baseInfo.setSurveyWay(cur.getString(column++));
            baseInfo.setSurveyTask(cur.getString(column++));
            baseInfo.setSurveySelect(cur.getString(column++));
            baseInfo.setRemark(cur.getString(column++));
            baseInfo.setTimeStamp(cur.getLong(column++));
            baseInfo.setLuji(cur.getInt(column++));
            baseInfo.setLumian(cur.getInt(column++));
            baseInfo.setQiaosui(cur.getInt(column++));
            baseInfo.setYanxian(cur.getInt(column));
            baseInfo.setIndex(index);
            investBaseInfos.add(baseInfo);
        }
        cur.close();
        db.close();

        return investBaseInfos;
    }
    @Override
    public void onCreate(SQLiteDatabase sqLiteDatabase) {
        sqLiteDatabase.execSQL(createSurveyTaskDataTable);
        sqLiteDatabase.execSQL(createRoadSurveyTable);
        sqLiteDatabase.execSQL(createSurveyTaskStructTable);
        sqLiteDatabase.execSQL(createSurveyTaskTable);
        sqLiteDatabase.execSQL(createSurveyTaskRouteTable);
        sqLiteDatabase.execSQL(createSurveyTaskRouteItemTable);
        sqLiteDatabase.execSQL(createAssetTongzuxinxi);

        sqLiteDatabase.execSQL(createRouteManage);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    }

    public void deleteOrgData(String orgNo){
        String sql="select ";
    }

    public void insertTaskStructInfo(List<RouteSurveyTaskStructBO> infos){
        if(infos==null||infos.size()<=0)
            return;
        Log.e(this.getClass().getSimpleName(),"init insertTaskStructInfo:"+infos.size());
        SQLiteDatabase db = getWritableDatabase();
        db.delete(TASKSTRUCTTB,null,null);
        ContentValues cv ;
        db.beginTransaction();
        for(RouteSurveyTaskStructBO info:infos){
            cv = new ContentValues();
            cv.put("ID", info.getiD());
            cv.put("taskDetailID", info.getTaskItemID());
            cv.put("name", info.getName());
            cv.put("startPotName", info.getStartPotName());
            cv.put("endPotName", info.getEndPotName() );
            cv.put("roadWidth", info.getRoadWidth() );
            cv.put("roadLength", info.getRoadLength() );
            cv.put("Pot", info.getPot());
            cv.put("PotKm", info.getPotKm());
            cv.put("PotMeter", info.getPotMeter());
            cv.put("state", info.getState() );
            db.insert(TASKSTRUCTTB, null, cv);
        }
        db.setTransactionSuccessful();
        db.endTransaction();
        db.close();
    }

    public void updateRouteSurveyTaskStructInfo(RouteSurveyTaskStructBO info,double width,int pos){
        SQLiteDatabase db=getWritableDatabase();

        ContentValues cv = new ContentValues();
        cv.put("startPotName", info.getStartPotName());
        cv.put("endPotName", info.getEndPotName() );
        cv.put("roadWidth", width);
        cv.put("roadLength", info.getRoadLength() );
        cv.put("childid",pos);
        db.update(TASKSTRUCTTB,cv,"ID=?",new String[]{info.getiD()});

        db.close();
    }
    public void updateRouteSurveyTaskDataBO(RouteSurveyTaskDataBO info){
        SQLiteDatabase db=getWritableDatabase();
        long time=System.currentTimeMillis();
        ContentValues cv = new ContentValues();
//        switch (pos){
//            case 0:
//                break;
//            case 1:
//                break;
//            case 2:
//                break;
//            case 3:
//                break;
//            case 4:
//                break;
//            case 5:
//                break;
//            case 6:
//                break;
//            case 7:
//                break;
//            case 8:
//                break;
//            case 9:
//                break;
//        }
        cv.put("surveyOne", info.getSurveyOne());
        cv.put("surveyTwo", info.getSurveyTwo() );
        cv.put("surveyThree", info.getSurveyThree() );
        cv.put("surveyFour", info.getSurveyFour() );
        cv.put("surveyFive", info.getSurveyFive());
        cv.put("surveySix", info.getSurveySix() );
        cv.put("surveySeven", info.getSurveySeven() );
        cv.put("surveyEight", info.getSurveyEight() );
        cv.put("surveyNine", info.getSurveyNine() );
        cv.put("surveyTen", info.getSurveyTen() );

        cv.put("checkOne", info.getCheckOne());
        cv.put("checkTwo", info.getCheckTwo() );
        cv.put("checkThree", info.getCheckThree() );
        cv.put("checkFour", info.getCheckFour() );
        cv.put("checkFive", info.getCheckFive());
        cv.put("checkSix", info.getCheckSix() );
        cv.put("checkSeven", info.getCheckSeven() );
        cv.put("checkEight", info.getCheckEight() );
        cv.put("checkNine", info.getCheckNine());
        cv.put("checkTen", info.getCheckTen() );
        cv.put("isupdated", 1);
        cv.put("updatetime", time);
        db.update(TASKDATATB,cv,"ID=?",new String[]{info.getiD()});

        db.close();
    }
//    public void updateRouteSurveyTaskDataBO(List<RouteSurveyTaskDataBO> infos,int pos){
//        SQLiteDatabase db=getWritableDatabase();
//        long time=System.currentTimeMillis();
//        ContentValues cv = new ContentValues();
//        switch (pos){
//            case 0:
//                break;
//            case 1:
//                break;
//            case 2:
//                break;
//            case 3:
//                break;
//            case 4:
//                break;
//            case 5:
//                break;
//            case 6:
//                break;
//            case 7:
//                break;
//            case 8:
//                break;
//            case 9:
//                break;
//        }
//        cv.put("surveyOne", info.getSurveyOne());
//        cv.put("surveyTwo", info.getSurveyTwo() );
//        cv.put("surveyThree", info.getSurveyThree() );
//        cv.put("surveyFour", info.getSurveyFour() );
//        cv.put("surveyFive", info.getSurveyFive());
//        cv.put("surveySix", info.getSurveySix() );
//        cv.put("surveySeven", info.getSurveySeven() );
//        cv.put("surveyEight", info.getSurveyEight() );
//        cv.put("surveyNine", info.getSurveyNine() );
//        cv.put("surveyTen", info.getSurveyTen() );
//
//        cv.put("checkOne", info.getCheckOne());
//        cv.put("checkTwo", info.getCheckTwo() );
//        cv.put("checkThree", info.getCheckThree() );
//        cv.put("checkFour", info.getCheckFour() );
//        cv.put("checkFive", info.getCheckFive());
//        cv.put("checkSix", info.getCheckSix() );
//        cv.put("checkSeven", info.getCheckSeven() );
//        cv.put("checkEight", info.getCheckEight() );
//        cv.put("checkNine", info.getCheckNine());
//        cv.put("checkTen", info.getCheckTen() );
//        cv.put("isupdated", 1);
//        cv.put("updatetime", time);
//        db.update(TASKDATATB,cv,"ID=?",new String[]{info.getiD()});
//
//        db.close();
//    }
    public void updateRouteSurveyUpdateDataInfo(List<Map<String, String>> infos){
        SQLiteDatabase db = getWritableDatabase();

        long time=System.currentTimeMillis();
        db.beginTransaction();
        for(Map<String,String> item: infos) {
            ContentValues cv = new ContentValues();
            for (String key : item.keySet()) {
                if(key.equals("id"))
                    continue;
                cv.put(key,item.get(key));
            }
            cv.put("isupdated", 1);
            cv.put("updatetime", time);
            db.update(TASKDATATB, cv, "id=?", new String[]{item.get("id").toString()});
        }
        db.setTransactionSuccessful();
        db.endTransaction();
        db.close();
    }
    /**
     * 插入初始化数据信息
     * @param info
     */
    public void insertInitData(InitDownLoadInfoBean info){
//        insertOrgInfo(info.getOrg());
//        insertCarsInfo(info.getCars());
//        insertRouteInfo(info.getRoute());
        insertTaskInfo(info.getTask());
        insertSurveyRouteInfo(info.getTaskDetail());
        insertTaskItemInfo(info.getTaskItem());
//        insertUserInfo(info.getUser());
        insertTaskStructInfo(info.getTaskStruct());
    }
    /**
     * 插入 调查任务信息
     *
     * @param infos
     */
    private void insertTaskInfo(List<RouteSurveyTaskBean> infos) {
        if(infos==null||infos.size()<=0)
            return;
        Log.e(this.getClass().getSimpleName(),"init insertTaskInfo:"+infos.size());
        SQLiteDatabase db = getWritableDatabase();
        db.delete(SURVEYTASKTB,null,null);
        ContentValues cv;
        db.beginTransaction();
        for (RouteSurveyTaskBean taskInfoBean : infos) {
            cv = new ContentValues();
            cv.put("ID", taskInfoBean.getiD());
            cv.put("org_No", taskInfoBean.getOrg_No());
            cv.put("taskName", taskInfoBean.getTaskName());
            db.insert(SURVEYTASKTB, null, cv);
        }
        db.setTransactionSuccessful();
        db.endTransaction();
        db.close();
    }
    /**
     * 插入 线路信息
     * @param infos
     */
    public void insertSurveyRouteInfo(List<RouteSurveyTaskDetailBean> infos){
        if(infos==null||infos.size()<=0)
            return;
        Log.e(this.getClass().getSimpleName(),"init RouteSurveyTaskDetailBean:"+infos.size());

        SQLiteDatabase db = getWritableDatabase();
        db.delete(SURVEY_ROUTETB,null,null);
        ContentValues cv;
        db.beginTransaction();
        for (RouteSurveyTaskDetailBean car : infos) {
            cv = new ContentValues();
            cv.put("ID", car.getiD());
            cv.put("taskID", car.getTaskID());
            cv.put("routeID", car.getRouteID() );
            cv.put("routeName", car.getRouteName() );
            cv.put("startPot", car.getStartPot() );
            cv.put("endPot", car.getEndPot() );
            cv.put("startPotKm", car.getStartPotKm());
            cv.put("startPotMeter", car.getStartPotMeter());
            cv.put("endPotKm", car.getEndPotKm());
            cv.put("endPotMeter", car.getEndPotMeter());
            db.insert(SURVEY_ROUTETB, null, cv);
        }
        db.setTransactionSuccessful();
        db.endTransaction();
        db.close();
    }
    private void insertTaskItemInfo(List<RouteSurveyTaskItemBO> infos){
        if(infos==null||infos.size()<=0)
            return;
        Log.e(this.getClass().getSimpleName(),"init insertTaskItemInfo:"+infos.size());
        SQLiteDatabase db = getWritableDatabase();
        db.delete(SURVEY_ROUTE_ITEMTB,null,null);
        ContentValues cv;
        db.beginTransaction();
        for(RouteSurveyTaskItemBO info:infos){
            cv = new ContentValues();
            cv.put("ID", info.getiD());
            cv.put("taskDetailID", info.getTaskDetailID());
            cv.put("startPot", info.getStartPot() );
            cv.put("endPot", info.getEndPot() );
            cv.put("startPotKm", info.getStartPotKm());
            cv.put("startPotMeter", info.getStartPotMeter());
            cv.put("endPotKm", info.getEndPotKm());
            cv.put("endPotMeter", info.getEndPotMeter());
            cv.put("roudBedType", info.getRoudBedType() );
            cv.put("routeGrade", info.getRouteGrade() );
            cv.put("potPeriod", info.getPotPeriod() );
            cv.put("surveyType",info.getSurveyType());
            db.insert(SURVEY_ROUTE_ITEMTB, null, cv);
        }
        db.setTransactionSuccessful();
        db.endTransaction();
        db.close();
    }
    /**
     * 获取所有任务信息
     *
     * @return
     */
    public List<RouteSurveyTaskBean> getTaskInfoAll(String org_No) {
        List<RouteSurveyTaskBean> taskInfoBeans = new ArrayList<RouteSurveyTaskBean>();
        SQLiteDatabase db = getReadableDatabase();
        Cursor cur = db.query(SURVEYTASKTB, null, "org_No=?", new String[]{String.valueOf(org_No)}, null, null, null);

        RouteSurveyTaskBean taskInfo;
        int column;
        while (cur.moveToNext()) {
            column = 0;
            taskInfo = new RouteSurveyTaskBean();
            taskInfo.setiD(cur.getString(column++));
            taskInfo.setPatrolDept(cur.getString(column++));
            taskInfo.setTaskName(cur.getString(column));

            taskInfoBeans.add(taskInfo);
        }
        cur.close();
        db.close();

        return taskInfoBeans;
    }
    /**
     * 获取所有任务信息
     *
     * @return
     */
    public List<RouteSurveyTaskBean> getTaskInfoAll() {
        List<RouteSurveyTaskBean> taskInfoBeans = new ArrayList<RouteSurveyTaskBean>();
        SQLiteDatabase db = getReadableDatabase();
        Cursor cur = db.query(SURVEYTASKTB, null, null, null, null, null, null);

        RouteSurveyTaskBean taskInfo;
        int column;
        while (cur.moveToNext()) {
            column = 0;
            taskInfo = new RouteSurveyTaskBean();
            taskInfo.setiD(cur.getString(column++));
            taskInfo.setPatrolDept(cur.getString(column++));
            taskInfo.setTaskName(cur.getString(column));

            taskInfoBeans.add(taskInfo);
        }
        cur.close();
        db.close();

        return taskInfoBeans;
    }
    /**
     * 根据任务 获取所有线路信息
     * @param taskID
     * @return
     */
    public List<RouteSurveyTaskDetailBean> getSurveyRouteInfo(String taskID){
        List<RouteSurveyTaskDetailBean> infoBeans = new ArrayList<RouteSurveyTaskDetailBean>();
        SQLiteDatabase db = getReadableDatabase();
        Cursor cur = db.query(SURVEY_ROUTETB, null, "taskID=?", new String[]{taskID}, null, null, null);

        RouteSurveyTaskDetailBean info;
        int column;
        while (cur.moveToNext()) {
            column = 0;
            info = new RouteSurveyTaskDetailBean();
            info.setiD(cur.getString(column++));
            info.setTaskID(cur.getString(column++));
            info.setRouteID(cur.getString(column++));
            info.setRouteName(cur.getString(column++));
            info.setStartPot(cur.getString(column++));
            info.setEndPot(cur.getString(column++)) ;
            info.setStartPotKm(cur.getInt(column++)) ;
            info.setStartPotMeter(cur.getInt(column++)) ;
            info.setEndPotKm(cur.getInt(column++)) ;
            info.setEndPotMeter(cur.getInt(column)) ;

            infoBeans.add(info);
        }
        cur.close();
        db.close();

        return infoBeans;
    }
    public List<RouteSurveyTaskItemBO> getSurveyTaskItemInfo(String taskID){
        List<RouteSurveyTaskItemBO> listInfo=new ArrayList<RouteSurveyTaskItemBO>();
        SQLiteDatabase db = getReadableDatabase();

        Cursor cursor=db.query(SURVEY_ROUTE_ITEMTB,null,"taskDetailID=?",new String[]{taskID},null,null,null );

        int column;
        while(cursor.moveToNext()){
            column=0;
            RouteSurveyTaskItemBO info=new RouteSurveyTaskItemBO();

            info.setiD(cursor.getString(column++));
            info.setTaskDetailID(cursor.getString(column++));
            info.setStartPot(cursor.getString(column++));
            info.setEndPot(cursor.getString(column++));
            info.setStartPotKm(cursor.getInt(column++));
            info.setStartPotMeter(cursor.getInt(column++));
            info.setEndPotKm(cursor.getInt(column++));
            info.setEndPotMeter(cursor.getInt(column++));
            info.setRoudBedType(cursor.getString(column++));
            info.setRouteGrade(cursor.getString(column++));
            info.setPotPeriod(cursor.getString(column++));
            info.setSurveyType(cursor.getString(column));

            listInfo.add(info);
        }

        db.close();

        return listInfo;
    }
    public List<RouteSurveyTaskStructBO> getRouteSurveyTaskStructInfo(String taskitemId,String state,TrafficInvestBaseInfo investBaseInfo){
        List<RouteSurveyTaskStructBO> infos=new ArrayList<RouteSurveyTaskStructBO>();
        SQLiteDatabase db = getReadableDatabase();

        int startKm=investBaseInfo.getStartPotKm();
        int endKm=investBaseInfo.getEndPotKm2();

        if(investBaseInfo.getEndPotMeter2()>0)
            endKm++;

        RouteSurveyTaskStructBO info;
        Cursor cur;
        if(state.equals("复核")){
            cur=db.query(TASKSTRUCTTB, null, "taskDetailID=? and state=? and PotKm >= ? AND PotKm<?  ",
                    new String[]{taskitemId + "", state,startKm+"",endKm+""}, null, null, "PotKm");
        }else
            cur=db.query(TASKSTRUCTTB, null, "taskDetailID=? and PotKm >= ? AND PotKm<?  ",
                    new String[]{taskitemId + "",startKm+"",endKm+""}, null, null, "PotKm");

        int column;
        while (cur.moveToNext()) {
            column = 0;
            info=new RouteSurveyTaskStructBO();
            info.setiD(cur.getString(column++));
            info.setTaskItemID(cur.getString(column++)) ;
            info.setName(cur.getString(column++));
            info.setStartPotName(cur.getString(column++)) ;
            info.setEndPotName(cur.getString(column++)) ;
            info.setRoadWidth(cur.getDouble(column++)) ;
            info.setRoadLength(cur.getDouble(column++)) ;
            info.setPot(cur.getString(column++));
            info.setPotKm(cur.getInt(column++));
            info.setPotMeter(cur.getInt(column++));
            info.setState(cur.getString(column)) ;

            infos.add(info);
        }
        cur.close();
        db.close();

        return infos;
    }
    public RouteSurveyTaskStructBO getTaskStructInfo(String taskStructId){
        SQLiteDatabase db=getWritableDatabase();
        Cursor cur=db.query(TASKSTRUCTTB, null, "id=?", new String[]{taskStructId}, null, null, null);
        RouteSurveyTaskStructBO info=new RouteSurveyTaskStructBO();
        if(cur.moveToFirst()){
            int column = 0;
            info.setiD(cur.getString(column++));
            info.setTaskItemID(cur.getString(column++)) ;
            info.setName(cur.getString(column++));
            info.setStartPotName(cur.getString(column++)) ;
            info.setEndPotName(cur.getString(column++)) ;
            info.setRoadWidth(cur.getDouble(column++)) ;
            info.setRoadLength(cur.getDouble(column++)) ;
            info.setPot(cur.getString(column++));
            info.setPotKm(cur.getInt(column++));
            info.setPotMeter(cur.getInt(column++));
            info.setState(cur.getString(column)) ;
        }
        cur.close();
        db.close();
        return  info;
    }
    public int getLastUpdateStructID(List<String> structIDs){
        String structId=null;

        if (structIDs.size() <= 0)
            return -1;

        StringBuilder sqlBuilder=new StringBuilder();
        sqlBuilder.append("select structID,MAX(updatetime) from ");
        sqlBuilder.append(TASKDATATB);

        sqlBuilder.append(" where structID in(");


        SQLiteDatabase db = getReadableDatabase();
        for (int i = 0; i < structIDs.size(); i++) {
            if(i>0)
                sqlBuilder.append(",");

            sqlBuilder.append("\"");
            sqlBuilder.append(structIDs.get(i));
            sqlBuilder.append("\"");
        }
        sqlBuilder.append(")");

        Cursor cursor= db.rawQuery(sqlBuilder.toString(),null);
        if(cursor.moveToNext()){
            structId=cursor.getString(0);
        }
        return structIDs.indexOf(structId);
    }
    public int getLastchildID(String id){
        int childid = -1;
        SQLiteDatabase db = getReadableDatabase();
        Cursor cur=db.query(TASKSTRUCTTB, null, "id=?", new String[]{id}, null, null, null);
        if(cur.moveToNext()){
            childid=cur.getInt(11);
        }
        return childid;
    }
    public List<RouteSurveyTaskDataBO> getRouteTaskDataInfo(List<String> structIDs) {
        List<RouteSurveyTaskDataBO> infos = new ArrayList<RouteSurveyTaskDataBO>();

        if (structIDs.size() <= 0)
            return infos;

        SQLiteDatabase db = getReadableDatabase();

        String[] params = structIDs.toArray(new String[]{});
        StringBuilder sb = new StringBuilder();
        for (int i = 0; i < structIDs.size(); i++) {
            sb.append(",");
            sb.append("?");
        }

        RouteSurveyTaskDataBO info = null;
        //  Cursor cur=db.query(TASKDATATB, null, null, null, null, null, null,"20");
        Cursor cur = db.query(TASKDATATB, null, "isupdated=1 and structID in(" + sb.substring(1) + ")", params, null, null, null);
        int column;
        while (cur.moveToNext()) {
            column = 0;
            info = new RouteSurveyTaskDataBO();
            info.setiD(cur.getString(column++));
            info.setColumnID(cur.getString(column++));
            info.setColumnName(cur.getString(column++));
            info.setUnit(cur.getString(column++));
            info.setNickName(cur.getString(column++));
            info.setStructID(cur.getString(column++));
            info.setType(cur.getString(column++));
            info.setSurveyOne(cur.getDouble(column++));
            info.setSurveyTwo(cur.getDouble(column++));
            info.setSurveyThree(cur.getDouble(column++));
            info.setSurveyFour(cur.getDouble(column++));
            info.setSurveyFive(cur.getDouble(column++));
            info.setSurveySix(cur.getDouble(column++));
            info.setSurveySeven(cur.getDouble(column++));
            info.setSurveyEight(cur.getDouble(column++));
            info.setSurveyNine(cur.getDouble(column++));
            info.setSurveyTen(cur.getDouble(column++));

            info.setCheckOne(cur.getDouble(column++));
            info.setCheckTwo(cur.getDouble(column++));
            info.setCheckThree(cur.getDouble(column++));
            info.setCheckFour(cur.getDouble(column++));
            info.setCheckFive(cur.getDouble(column++));
            info.setCheckSix(cur.getDouble(column++));
            info.setCheckSeven(cur.getDouble(column++));
            info.setCheckEight(cur.getDouble(column++));
            info.setCheckNine(cur.getDouble(column++));
            info.setCheckTen(cur.getDouble(column++));

            info.setSortID(cur.getInt(column));

            infos.add(info);
        }
        return infos;

    }

    /*
        路线查询
     */


    //插入
    public void insertRouteManage(List<RouteManage> infos){
        if(infos==null||infos.size()<=0)
            return;
        SQLiteDatabase db = getWritableDatabase();
        db.delete(ROUTEMANAGE,null,null);
        ContentValues cv ;
        db.beginTransaction();
        for(RouteManage info:infos){
            cv = new ContentValues();
            cv.put("ID", info.getId());
            cv.put("routeId", info.getRouteId());
            cv.put("routeCode", info.getRouteCode());
            cv.put("routeName", info.getRouteName());
            cv.put("startPot", info.getStartPot() );
            cv.put("endPot", info.getEndPot() );
            cv.put("custodyRouteMileage", info.getCustodyRouteMileage() );
            cv.put("custodyRouteType", info.getCustodyRouteType());
            cv.put("orgId", info.getOrgId());
            cv.put("orgName", info.getOrgName());
            db.insert(ROUTEMANAGE, null, cv);
        }
        db.setTransactionSuccessful();
        db.endTransaction();
        db.close();
    }
    //获取全部
    public List<RouteManage> getRouteManages(){
        List<RouteManage> infos=new ArrayList<RouteManage>();
        SQLiteDatabase db = getReadableDatabase();

        RouteManage info=null;
        Cursor cur=db.query(ROUTEMANAGE, null, null, null, null, null, null);
        int column;
        while (cur.moveToNext()) {
            column = 0;
            info=new RouteManage();
            info.setId(cur.getString(column++));
            info.setRouteId(cur.getString(column++));
            info.setRouteCode(cur.getString(column++));
            info.setRouteName(cur.getString(column++));
            info.setStartPot(cur.getString(column++));
            info.setEndPot(cur.getString(column++));
            info.setCustodyRouteMileage(cur.getDouble(column++));
            info.setCustodyRouteType(cur.getString(column++));
            info.setOrgId(cur.getString(column++));
            info.setOrgName(cur.getString(column));

            infos.add(info);
        }
        cur.close();
        db.close();

        return infos;
    }
    //获取每个分局全部公路类型
    public List<String> getRouteManageTypes(String orgName){
        List<String> infos=new ArrayList<String>();
        SQLiteDatabase db = getReadableDatabase();

        String info=null;
        Cursor cur=db.query(true, ROUTEMANAGE, new String[]{"custodyRouteType"}, "orgName=?", new String[]{orgName}, "custodyRouteType", null, "custodyRouteType asc", null, null);
        int column;
        while (cur.moveToNext()) {
            column = 0;
            info = cur.getString(column);

            infos.add(info);
        }
        cur.close();
        db.close();

        return infos;
    }

    //获取全部公路类型
    public List<String> getRouteManageTypes(){
        List<String> infos=new ArrayList<String>();
        SQLiteDatabase db = getReadableDatabase();

        String info=null;
        Cursor cur=db.query(true, ROUTEMANAGE, new String[]{"custodyRouteType"}, null, null, "custodyRouteType", null, "custodyRouteType asc", null, null);
        int column;
        while (cur.moveToNext()) {
            column = 0;
            info = cur.getString(column);

            infos.add(info);
        }
        cur.close();
        db.close();

        return infos;
    }
    //获取对应公路类型下的路线
    public List<RouteType> getTypes(List<String> typeList){
//        List<String> typeList = getRouteManageTypes();
        List<RouteType> infos=new ArrayList<RouteType>();
        RouteType info=null;
        SQLiteDatabase db = getReadableDatabase();
        Cursor cur = null;
        for (int i = 0; i<typeList.size(); i++){
            info = new RouteType();
            String custype = typeList.get(i);
            info.custodyRouteType = custype;
            List<RouteManage> rms=new ArrayList<RouteManage>();
            RouteManage rm=null;
            cur = db.query(ROUTEMANAGE, null, "custodyRouteType=?", new String[]{custype}, null, null, null);
            int column;
            int index = 0;
            while (cur.moveToNext()) {
                column = 0;
                index = index+1;
                rm=new RouteManage();
                rm.index = index;
                rm.setId(cur.getString(column++));
                rm.setRouteId(cur.getString(column++));
                rm.setRouteCode(cur.getString(column++));
                rm.setRouteName(cur.getString(column++));
                rm.setStartPot(cur.getString(column++));
                rm.setEndPot(cur.getString(column++));
                rm.setCustodyRouteMileage(cur.getDouble(column++));
                rm.setCustodyRouteType(cur.getString(column++));
                rm.setOrgId(cur.getString(column++));
                rm.setOrgName(cur.getString(column));
                rms.add(rm);
            }
            info.routeManages = rms;
            infos.add(info);
        }
        assert cur != null;
        cur.close();
        db.close();
        return infos;
    }
    //获取对应公路类型下的路线
    public List<RouteType> getTypes(List<String> typeList, String orgName){
//        List<String> typeList = getRouteManageTypes();
        List<RouteType> infos=new ArrayList<RouteType>();
        RouteType info=null;
        SQLiteDatabase db = getReadableDatabase();
        Cursor cur = null;
        for (int i = 0; i<typeList.size(); i++){
            info = new RouteType();
            String custype = typeList.get(i);
            info.custodyRouteType = custype;
            List<RouteManage> rms=new ArrayList<RouteManage>();
            RouteManage rm=null;
            cur = db.query(ROUTEMANAGE, null, "custodyRouteType=? and orgName=?", new String[]{custype,orgName}, null, null, null);
            int column;
            int index = 0;
            while (cur.moveToNext()) {
                column = 0;
                index = index+1;
                rm=new RouteManage();
                rm.index = index;
                rm.setId(cur.getString(column++));
                rm.setRouteId(cur.getString(column++));
                rm.setRouteCode(cur.getString(column++));
                rm.setRouteName(cur.getString(column++));
                rm.setStartPot(cur.getString(column++));
                rm.setEndPot(cur.getString(column++));
                rm.setCustodyRouteMileage(cur.getDouble(column++));
                rm.setCustodyRouteType(cur.getString(column++));
                rm.setOrgId(cur.getString(column++));
                rm.setOrgName(cur.getString(column));
                rms.add(rm);
            }
            info.routeManages = rms;
            infos.add(info);
        }
        assert cur != null;
        cur.close();
        db.close();
        return infos;
    }

    //获取所有里程
    public double getRouteManageMiles(){
        SQLiteDatabase db = getReadableDatabase();
        Cursor cur = db.rawQuery("select sum(custodyRouteMileage) from routemanage",null);
        cur.moveToFirst();
        double info = cur.getDouble(0);
        cur.close();
        db.close();
        return info;
    }
//    //获取所有里程
//    public double getRouteManageMiles(String type,String org){
//        SQLiteDatabase db = getReadableDatabase();
//        Cursor cur = db.rawQuery("select sum(custodyRouteMileage) from routemanage where orgName = "+ org +" and custodyRouteType = "+type,null);
//        cur.moveToFirst();
//        double info = cur.getDouble(0);
//        cur.close();
//        db.close();
//        return info;
//    }
    //获取总局全部分局
    public List<String> getRouteManageSubofficeNames(){
        List<String> infos=new ArrayList<String>();
        SQLiteDatabase db = getReadableDatabase();

        String info=null;
        Cursor cur=db.query(true, ROUTEMANAGE, new String[]{"orgName"}, null, null, "orgName", null, null, null, null);
        int column;
        while (cur.moveToNext()) {
            column = 0;
            info = cur.getString(column);

            infos.add(info);
        }
        cur.close();
        db.close();

        return infos;
    }
//    //获取全部
//    public List<SubOffice> getSubOffices(){
//        List<SubOffice> subOffices = getSubOffices();
//
//        List<String> typeList = getRouteManageTypes();
//        List<RouteType> infos=new ArrayList<RouteType>();
//        RouteType info=null;
//        SQLiteDatabase db = getReadableDatabase();
//        Cursor cur = null;
//        for (int i = 0; i<typeList.size(); i++){
//            info = new RouteType();
//            String custype = typeList.get(i);
//            info.custodyRouteType = custype;
//            List<RouteManage> rms=new ArrayList<RouteManage>();
//            RouteManage rm=null;
//            cur = db.query(ROUTEMANAGE, null, "custodyRouteType=?", new String[]{custype}, null, null, null);
//            int column;
//            while (cur.moveToNext()) {
//                column = 0;
//                rm=new RouteManage();
//                rm.setId(cur.getString(column++));
//                rm.setRouteId(cur.getString(column++));
//                rm.setRouteCode(cur.getString(column++));
//                rm.setRouteName(cur.getString(column++));
//                rm.setStartPot(cur.getString(column++));
//                rm.setEndPot(cur.getString(column++));
//                rm.setCustodyRouteMileage(cur.getDouble(column++));
//                rm.setCustodyRouteType(cur.getString(column++));
//                rm.setOrgId(cur.getString(column++));
//                rm.setOrgName(cur.getString(column));
//                rms.add(rm);
//            }
//            info.routeManages = rms;
//            infos.add(info);
//        }
//        assert cur != null;
//        cur.close();
//        db.close();
//        return infos;
//    }
    /**
     * 插入 路况调查基本信息 数据
     *
     * @param investInfo
     */
    public void insertAssetTong(AssetTongzuxinxi investInfo) {
        SQLiteDatabase db = getWritableDatabase();
        ContentValues cv = new ContentValues();
        cv.put("ID", investInfo.id);
        cv.put("status", investInfo.status);
        cv.put("blockTime", investInfo.blockTime);
        cv.put("routeCoding", investInfo.routeCoding);
        cv.put("routeId", investInfo.routeId);
        cv.put("startLocation", investInfo.startLocation);
        cv.put("endLocation", investInfo.endLocation);
        cv.put("description", investInfo.description);
        cv.put("recoveryTime", investInfo.recoveryTime);
        cv.put("plan", investInfo.plan);
        cv.put("startPot", investInfo.startPot);
        cv.put("startPotK", investInfo.startPotK);
        cv.put("startPotKm", investInfo.startPotKm);
        cv.put("startPotM", investInfo.startPotM);
        cv.put("endPot", investInfo.endPot);
        cv.put("endPotK", investInfo.endPotK);
        cv.put("endPotKm", investInfo.endPotKm);
        cv.put("endPotM", investInfo.endPotM);
        cv.put("upDown",investInfo.upDown);
        cv.put("title", investInfo.title);
        cv.put("orgId", investInfo.orgId);
        cv.put("orgName", investInfo.orgName);
        cv.put("smoothreCoveryTime", investInfo.smoothreCoveryTime);
        cv.put("reportingStatus", investInfo.reportingStatus);
        cv.put("isRetimeUp", investInfo.isRetimeUp);
        cv.put("isDataUp",investInfo.isDataUp);
        cv.put("imgs",investInfo.imgs);
        db.insert(ATongzuxinxi, null, cv);

        db.close();
    }
    public List<AssetTongzuxinxi> getAssetTongs(){
        List<AssetTongzuxinxi> infos=new ArrayList<AssetTongzuxinxi>();
        SQLiteDatabase db = getReadableDatabase();

        AssetTongzuxinxi info=null;
        Cursor cur=db.query(ATongzuxinxi, null, null, null, null, null, null);
        int column;
        int index = 0;
        while (cur.moveToNext()) {
            column = 0;
            index = index+1;
            info=new AssetTongzuxinxi();
            info.index = index;
            info.id = (cur.getString(column++));
            info.status = (cur.getString(column++)) ;
            info.blockTime = (cur.getString(column++)) ;
            info.routeCoding = (cur.getString(column++)) ;
            info.routeId = (cur.getString(column++)) ;
            info.startLocation = (cur.getString(column++)) ;
            info.endLocation = (cur.getString(column++)) ;
            info.description = (cur.getString(column++)) ;
            info.recoveryTime = (cur.getString(column++)) ;
            info.plan = (cur.getString(column++)) ;
            info.startPot = (cur.getString(column++)) ;
            info.startPotK = (cur.getString(column++)) ;
            info.startPotKm = (cur.getLong(column++)) ;
            info.startPotM = (cur.getLong(column++)) ;
            info.endPot = (cur.getString(column++)) ;
            info.endPotK = (cur.getString(column++)) ;
            info.endPotKm = (cur.getLong(column++));
            info.endPotM = (cur.getLong(column++)) ;
            info.upDown = (cur.getString(column++)) ;
            info.title = (cur.getString(column++)) ;
            info.orgId = (cur.getString(column++)) ;
            info.orgName = (cur.getString(column++)) ;
            info.smoothreCoveryTime = (cur.getString(column++)) ;
            info.reportingStatus = (cur.getString(column++)) ;
            info.isRetimeUp = (cur.getInt(column++)) ;
            info.isDataUp = (cur.getInt(column++)) ;
            info.imgs = (cur.getString(column));


            infos.add(info);
        }
        cur.close();
        db.close();

        return infos;
    }
//    public void updateBlockReTime(AssetTongzuxinxi info){
//        SQLiteDatabase db=getWritableDatabase();
//        ContentValues cv = new ContentValues();
//        cv.put("smoothreCoveryTime", info.smoothreCoveryTime);
//        cv.put("isRetimeUp",info.isRetimeUp);
//        cv.put("reportingStatus",info.reportingStatus);
//        db.update(ATongzuxinxi,cv,"id=?",new String[]{info.id});
//        db.close();
//    }
//    public void updateBlockReTimeStatus(AssetTongzuxinxi info){
//        SQLiteDatabase db=getWritableDatabase();
//        ContentValues cv = new ContentValues();
//        cv.put("smoothreCoveryTime", info.smoothreCoveryTime);
//        cv.put("isRetimeUp",info.isRetimeUp);
//        cv.put("reportingStatus",info.reportingStatus);
//        db.update(ATongzuxinxi,cv,"id=?",new String[]{info.id});
//        db.close();
//    }
    public void updateBlockData(AssetTongzuxinxi investInfo){
        SQLiteDatabase db=getWritableDatabase();

        ContentValues cv = new ContentValues();
        cv.put("status", investInfo.status);
        cv.put("blockTime", investInfo.blockTime);
        cv.put("routeCoding", investInfo.routeCoding);
        cv.put("routeId", investInfo.routeId);
        cv.put("startLocation", investInfo.startLocation);
        cv.put("endLocation", investInfo.endLocation);
        cv.put("description", investInfo.description);
        cv.put("recoveryTime", investInfo.recoveryTime);
        cv.put("plan", investInfo.plan);
        cv.put("startPot", investInfo.startPot);
        cv.put("startPotK", investInfo.startPotK);
        cv.put("startPotKm", investInfo.startPotKm);
        cv.put("startPotM", investInfo.startPotM);
        cv.put("endPot", investInfo.endPot);
        cv.put("endPotK", investInfo.endPotK);
        cv.put("endPotKm", investInfo.endPotKm);
        cv.put("endPotM", investInfo.endPotM);
        cv.put("upDown",investInfo.upDown);
        cv.put("title", investInfo.title);
        cv.put("orgId", investInfo.orgId);
        cv.put("orgName", investInfo.orgName);
        cv.put("smoothreCoveryTime", investInfo.smoothreCoveryTime);
        cv.put("reportingStatus", investInfo.reportingStatus);
        cv.put("isRetimeUp", investInfo.isRetimeUp);
        cv.put("isDataUp",investInfo.isDataUp);
        cv.put("imgs",investInfo.imgs);
        db.update(ATongzuxinxi,cv,"id=?",new String[]{investInfo.id});

        db.close();
    }
}
